#! /bin/bash
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "

--'每天/每月/每年线上线下以及新老学员的意向用户个数'
insert into edu_rpt.rpt_dt_origin_nd
select
*
from edu_dws.dws_dt_origin_nd
order by group_type desc,y,d,m;

--每天/每月/每年各地区的线上线下以及新老学员的意向用户个数
insert into edu_rpt.rpt_dt_area_origin_nd
SELECT
    *
FROM edu_dws.dws_dt_area_origin_nd
order by group_type desc,y,d,m;

--每天/每月/每年各学科线上线下以及新老学员的意向用户个数Top10
insert into edu_rpt.rpt_dt_sub_origin_nd
WITH tmp as (
    SELECT y,
           m,
           d,
           or_sta,
           sub_name,
           group_type,
           cou_id,
           dense_rank() over(partition by y,m,d,or_sta order by cou_id) AS "topn"
    FROM edu_dws.dws_dt_sub_origin_nd
)
SELECT
    *
FROM tmp
where topn<=10
order by group_type DESC,y,m,d,or_sta;

--每天/每月/每年各校区线上线下以及新老学员的意向用户个数Top10
insert into edu_rpt.rpt_dt_sch_origin_nd
WITH tmp as (
    SELECT y,
           m,
           d,
           or_sta,
           sch_name,
           group_type,
           cou_id,
           dense_rank() over(partition by y,m,d,or_sta order by cou_id DESC ) AS "topn"

    FROM edu_dws.dws_dt_sch_origin_nd
)
SELECT
    *
FROM tmp
where topn<=10
order by group_type DESC,y,m,d,or_sta;


--每天/每月/每年各来源渠道线上线下以及新老学员的意向用户个数
insert into edu_rpt.rpt_dt_oc_origin_nd
SELECT
    *
FROM edu_dws.dws_dt_oc_origin_nd
order by group_type desc,y,d,m;

--每天/每月/每年各咨询中心线上线下以及新老学员的意向用户个数
insert into edu_rpt.rpt_dt_dep_origin_nd
SELECT
    *
FROM edu_dws.dws_dt_dep_origin_nd
order by group_type desc,y,d,m;

--每天线上线下及新老学员的有效线索个数
insert into edu_rpt.rpt_day_origin_nd_count
with tmp as (
    SELECT y,
           m,
           d,
           or_sta,
           appeal_status,
           group_type,
           SUM(cou_id) OVER(PARTITION BY d) as "sum_all"
    FROM edu_dws.dws_dt_line_nd_sta
    WHERE group_type = 'day_line_nd_sta'
)
select
    tmp.y AS "y",
    tmp.m AS "m",
    tmp.d AS "d",
    tmp.or_sta AS "or_sta",
    tmp.appeal_status AS "appeal_status",
    tmp.group_type AS "group_type",
    "sum_all" - "sum_others" AS "count_true"
from tmp
join (
    SELECT y,
           m,
           d,
           or_sta,
           appeal_status,
           group_type,
           SUM(cou_id) OVER(PARTITION BY d) as "sum_others"
    FROM edu_dws.dws_dt_line_nd_sta
    WHERE group_type = 'day_line_nd_sta'
      and appeal_status = 'others'
)   as aaa1
on tmp.d=aaa1.d;


--每小时线上线下及新老学员的有效线索转化率 = 有效线索个数 / 总线索个数
insert into edu_rpt.rpt_hour_origin_nd_eff
with tmp as (
    SELECT y,
           m,
           d,
           h,
           or_sta,
           appeal_status,
           group_type,
           cou_id,
           SUM(cou_id) OVER(PARTITION BY h) AS "sum_true"
    FROM hive.edu_dws.dws_dt_line_nd_sta
    WHERE group_type = 'hour_dep_line_nd'
)
select
    tmp.y AS "y",
    tmp.m AS "m",
    tmp.d AS "d",
    tmp.h AS "h",
    tmp.or_sta AS "or_sta",
    tmp.appeal_status AS "appeal_status",
    tmp.group_type AS "group_type",
    tmp.cou_id AS "cou_id",
    ((1-("sum_true"/"sum_all")) * 1.00)  AS "efficient"
from tmp
join(
    SELECT
        y,
        m,
        d,
        h,
        or_sta,
        appeal_status,
        group_type,
        cou_id,
        SUM(cou_id) OVER(PARTITION BY h) as "sum_all"
    FROM hive.edu_dws.dws_dt_line_nd_sta
    WHERE group_type = 'hour_dep_line_nd'AND appeal_status = 'others'
) as SUM_tmp
ON tmp.h=SUM_tmp.h;

-----------------------------------------------------------------------


--'每天/每月/每年线上线下以及新老学员的意向用户个数'

CREATE TABLE mysql.edu_rpt.rpt_dt_origin_nd as
select
    y,
    m,
    d,
    CAST(or_sta AS varchar) as or_sta,
    group_type,
    cou_id
from hive.edu_rpt.rpt_dt_origin_nd;

--每天/每月/每年各地区的线上线下以及新老学员的意向用户个数

CREATE TABLE mysql.edu_rpt.rpt_dt_area_origin_nd as
select
    y,
    m,
    d,
    cast(or_sta AS varchar) as or_sta,
    cast(area AS varchar) as area,
    group_type,
    cou_id
from hive.edu_rpt.rpt_dt_area_origin_nd;

--每天/每月/每年各学科的线上线下以及新老学员的意向用户个数top10
CREATE TABLE mysql.edu_rpt.rpt_dt_sub_origin_nd AS
select
    y,
    m,
    d,
    CAST(or_sta AS VARCHAR ) AS or_sta,
    CAST(sub_name AS VARCHAR ) AS sub_name,
    group_type,
    cou_id,
    topn
from hive.edu_rpt.rpt_dt_sub_origin_nd;

--'每天/每月/每年/各校区/线上线下/新老客户的意向用户个数统计top10'
CREATE TABLE mysql.edu_rpt.rpt_dt_sch_origin_nd AS
select
    y,
    m,
    d,
    CAST(or_sta AS VARCHAR ) AS or_sta,
    CAST(sch_name AS VARCHAR ) AS sch_name,
    group_type,
    cou_id,
    topn
from hive.edu_rpt.rpt_dt_sch_origin_nd;

--'每天/每月/每年/各来源渠道/线上线下/新老客户的意向用户个数统计'
CREATE TABLE mysql.edu_rpt.rpt_dt_oc_origin_nd as
select
    y,
    m,
    d,
    cast(or_sta AS varchar) as or_sta,
    cast(origin_channel AS varchar) as origin_channel,
    group_type,
    cou_id
from hive.edu_rpt.rpt_dt_oc_origin_nd;

--'每天/每月/每年/各咨询中心/线上线下/新老客户的意向用户个数统计'
CREATE TABLE mysql.edu_rpt.rpt_dt_dep_origin_nd as
select
    y,
    m,
    d,
    cast(or_sta AS varchar) as or_sta,
    cast(dep_name AS varchar) as dep_name,
    group_type,
    cou_id
from hive.edu_rpt.rpt_dt_dep_origin_nd;

--每天线上线下新老学员有效线索个数
CREATE TABLE mysql.edu_rpt.rpt_day_origin_nd_count as
select
    y,
    m,
    d,
    cast(or_sta AS varchar) as or_sta,
    appeal_status,
    group_type,
    count_true
from hive.edu_rpt.rpt_day_origin_nd_count;

--'每小时/线上线下/新老客户有效线索率'
CREATE TABLE mysql.edu_rpt.rpt_hour_origin_nd_eff  as
select
    y,
    m,
    d,
    h,
    cast(or_sta AS varchar) as or_sta,
    appeal_status,
    group_type,
    cou_id,
    efficient
from hive.edu_rpt.rpt_hour_origin_nd_eff;